HW#4 --- last modified March 02 2019 21:20:35..

Solution set.

Due date: Nov 16

Files to be submitted:
  Market.sql
  MarketPlace.java
  MarketPlace.sqlj

Purpose: To experiment with writing transactions in SQLJ and JDBC.

Specification:

For this homework you will design a database as well as three transactions which will be useful in modeling a variation of a double auction marketplace. You will submit three files: Market.sql, which has all of the SQL needed to create and populate your marketplace; MarketClient.java, which has a JDBC implementation of the code to perform the transactions with the database; and MarketClient.sqlj, which has an SQLJ implementation of the code to perform the transaction with the database.

As mentioned above Market.sql creates a marketplace for our double auction. A market place consists of a TRADER table, and an OFFERS table. Each TRADER has a Name, an Id, a Type (either buyer or seller), a quantity of Dollars, and a quantity of Euros. Id is the primary key of this table. Rows in the OFFERS table consist of an OfferID (which is the primary key), a ProposedPrice in dollars, and an Amount of Euros. TraderID is a foreign key constraint referencing TRADER.Id. Your Market.sql script should create these two tables, implement the key constraints, as well as the domain constraint on TRADER.Type that it is either buyer or seller. In addition, there should be check on OFFERS that if the TraderID is of Type buyer then the ProposedPrice*Amount must be less than the number of Dollars this TRADER has. Similarly, if the TRADER is Type seller then Amount must be less than the number of Euros listed for this TRADER. You should use triggers to do this. After building the tables your script should insert five buyers and five sellers into the TRADER table. Each buyer should start with $100 and 0 euros; each seller should start with $0 and a 100 euros.

MarketPlace.java and MarketPlace.sqlj do exactly the same things -- the only difference is that the first is written using JDBC and the second is written using SQLJ. That said, your JDBC code should be handwritten -- it will receive no credit if it is just the result of running sqlj on the SQLJ implementation. Each program will be run from the command line with a line like:

java MarketPlace

Each program should then list out to System.out a choice of three operations to choose from: (1) Print out current OFFERS, (2) Make an offer, (3) Accept an offer. The user can then choose which option they want. As you might guess, (1) lists out all of the rows in the OFFERS table. If (2) is chosen, then the user is prompted for the ID of a trader, a ProposedPrice, and an Amount. These are used to try to insert a row into the OFFERS table of your Oracle Database. This insert may fail if one of the constraints listed above is violated. So your program should indicate if your operation succeeded or not. Finally, if (3) is selected, the user is prompted for a TraderID, and an OfferID. The idea is the TRADER given by TraderID is supposed to be accepting the offer given by OfferID. This can only make sense if the TRADER in question is of different Type then the one that made the offer. That is, one must be a buyer and the other a seller. Further, if the trader accepting is a buyer and does not have the highest current buyer offer in the OFFERS table then the accept transaction fails. If the buyer does have current highest bid he buys up to the lesser amount among the amount in his original bid and the amount in the offer he is accepting. After the deal goes through, each of the two offer rows if adjusted. One will now have an amount 0 and should be deleted from the table. Also, the number of Dollars the buyer has should be reduced by the amount purchased * purchase price, the seller should have his dollars increased by this amount. The number of Euros the seller he bought from has should be reduced by the amount of the transaction and the buyer should get this many euros. In the case where the TRADER accepting a bid is a seller analogous rules apply: If a seller does not have the lowest selling price among all sellers in the OFFERS table then the accept transaction fails. If the seller does have the lowest selling price, he is entitled to sell to the buyer he accepts the offer from, the lesser amount amoung the amount he listed he had been willing to sell and the amount the buyer had listed as being willing to buy. The seller gets the price for his goods the buyer offered. The seller's row and this buyer's row should have their amounts adjusted. One row will be now 0 and should be deleted from the offers table. The number of dollars the seller TRADER has should be increased by the sold amount * purchase price and that of the buyer should be reduced by this quantity. The amount of euros the seller has should go down by the amount sold; the buyer should get this many euros.

After completing one of the operations (1), (2), or (3) above the user should be offered the opportunity to choose another operation or quit.

Point Breakdown